#!/usr/bin/env python2
# create_database.py
"""This module is intended to create a database and demonstrate sqlite tools"""
import sqlite3, os, glob, sys
import calendar


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
def main():
    """main function creates a database from the files in the current directory"""
    path = '../data/'
    data = []
    for infile in glob.glob( os.path.join(path, 'test*.txt') ):
        print "current file is: " + infile 
        datum = FileDatum(infile)
        data.append(datum)
    db = Database(createDB("ciTests.db"))
    db.loadresults(data)

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
def createDB(name) :
    """Creates an empty database with the given name.
    Returns a connection the database, which will have the appropriate 
    (empty) tables.
    """
    # Create a Python object representating the db.
    if os.path.exists(name):
        raise Exception, "Error, file " + name + " already exists."
    conn = sqlite3.connect(name)
    # Create the appropraite tables.
    conn.execute("CREATE TABLE PersonTable(\
            personID INTEGER PRIMARY KEY, \
            username TEXT,\
            birthyear INTEGER,\
            birthmonth INTEGER,\
            gender TEXT,\
            CI INTEGER)")
    conn.execute("CREATE TABLE ResultsTable(\
            resultID INTEGER PRIMARY KEY,\
            filename TEXT,\
            personID INTEGER,\
            month INTEGER,\
            day INTEGER,\
            hour INTEGER,\
            minute INTEGER,\
            second INTEGER,\
            volume INTEGER,\
            range INTEGER,\
            discrimination INTEGER)")
    return conn

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
def connect(db) :
    """If the database doesn't exist, report as much.
    Otherwise delete any copies currently in the temporary directory, 
    make a new copy there, and connect to it.
    """
    if not os.path.exists(db) :
        raise Exception, "Error: could not find specified database."
    tDir = tempfile.gettempdir() + "/"
    #if os.path.exists(tDir + db) :
    #       os.remove(tDir + db)
    if os.path.exists(tDir + 'ciTests.db') :
        os.remove(tDir + 'ciTests.db')
    shutil.copy(db, tDir + 'ciTests.db')
    return sqlite3.connect(tDir + 'ciTests.db')

# --------------------------------------------------------------------------- 
class Database:
    def __init__(self, dbConn):
        """creates an analog to the actual database"""
        self.conn = dbConn
    def loadresults(self, filedata):
        """loads all of the results objects into the database"""

        c = self.conn.cursor()

        # make person table
        users = []
        personID=0
        for datum in filedata:
            # gather the current usernames in the table
            try :
                c.execute("SELECT username FROM PersonTable")
            except sqlite3.OperationalError, e :
                raise InputException, "Table 'PersonTable' is missing or formatted incorrectly"
            for row in c :
                users.append(row[0])
            # unless there's a duplicate
            if datum.username in users :
                print "The user " + datum.username + " is a repeat test taker.\n"
            # enter the entry
            else : 
                personID +=1
                print "The person data for : " + datum.username + " is here loaded.\n"
                self.conn.execute("INSERT into \
                        PersonTable(personID,username,birthmonth,birthyear,gender,CI) values\
                        ( ?, ?, ?, ?, ?, ?)" ,\
                        (personID,\
                        datum.username,\
                        datum.birthmonth,\
                        datum.birthyear,\
                        datum.gender,\
                        datum.CI,))

        # make result table
        for datum in filedata:
            resultID = int(datum.filename.strip("../data/test").strip(".txt"))
            IDrows = self.conn.execute("SELECT personID from PersonTable WHERE username = (?)",(datum.username,))
            for row in IDrows:
                personID = row[0]
            print "The result data for : " + datum.filename + " is here loaded."
            self.conn.execute("INSERT into \
                    ResultsTable(resultID,filename,personID,month,day,hour,minute,second,volume,range,discrimination) \
                    values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" ,\
                    (resultID ,\
                    datum.filename,\
                    personID,\
                    datum.month,\
                    datum.day,\
                    datum.hr,\
                    datum.min,\
                    datum.sec,\
                    datum.volume,\
                    datum.range,\
                    datum.discrimination,))
            ## using the dbConn, fill the results table 
            ## fill the people table
        # commits the changes
        self.conn.commit()


# --------------------------------------------------------------------------- 
class FileDatum:
    """A single set of test results (one file)"""
    def __init__(self,filename):
        """Initializes the result data corresponding to the data in the file"""
        self.filename = filename
        self.discimination = 0
        f = open(filename, 'r')
        lines = f.readlines()
        for line in lines :
            words = line.split(" ")
            if "Reported:" in words : 
                self.day = words[3]
                monthname = words[2]
                self.month = 0
                for idx in range(1,13):
                    if monthname == calendar.month_abbr[idx]:
                        self.month == idx
                time = words[4].split(":")
                self.hr  = int(time[0])
                self.min = int(time[1])
                self.sec = int(time[2])
                self.yr = int(words[5])
            elif "Subject:" in words : 
                self.username = words[1]
            elif "Year/month" in words : 
                birth= words[3].split("/")
                self.birthyear = int(birth[0])
                self.birthmonth = int(birth[1])
            elif "Sex:" in words : 
                self.gender= words[1]
            elif "CI" in words : 
                self.CI = int(words[2])
            elif "Volume:" in words : 
                try : 
                    self.volume = int(words[1])
                except ValueError :
                    print "Be forewarned, there is a missing volume entry."
            elif "Range:" in words :
                try : 
                    self.range = int(words[1])
                except ValueError :
                    print "Be forewarned, there is a missing range entry."
            elif "Discrimination:" in words :
                try : 
                    self.discrimination = int(words[1])
                except ValueError :
                    print "Be forewarned, there is a missing discrimination entry."

   
if __name__=='__main__':
    sys.exit(main())

